package com.pai.dao.Personnel;

import com.mysql.cj.util.StringUtils;
import com.pai.dao.BaseDao;
import com.pai.entity.Personnel;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class PersonnelDaoImpl implements PersonnelDao {
    /**
     * 获取列表
     * @param connection
     * @return
     * @throws Exception
     */
    @Override
    public List<Personnel> getList(Connection connection) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Personnel> resultList = new ArrayList<Personnel>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from personnel, department, appuser where personnel.departmentId = department.id and personnel.accountId = appuser.id");

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Personnel _item = new Personnel();
            _item.setId(rs.getInt("personnel.id"));
            _item.setName(rs.getString("personnel.name"));
            _item.setSex(rs.getInt("personnel.sex"));
            _item.setWorkno(rs.getString("personnel.workno"));
            _item.setPhone(rs.getString("personnel.phone"));
            _item.setDepartmentId(rs.getInt("department.id"));
            _item.setDepartmentType(rs.getInt("department.depType"));
            _item.setDepartmentName(rs.getString("department.depName"));
            _item.setAccountId(rs.getInt("appuser.id"));
            _item.setAccountName(rs.getString("appuser.account"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     *
     * @param connection
     * @param departmentId  部门id
     * @param depName 部门名称
     * @param perName 人员名称
     * @return
     * @throws Exception
     */
    @Override
    public List<Personnel> getList(Connection connection, String departmentId, String depName, String perName) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Personnel> resultList = new ArrayList<Personnel>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from personnel, department, appuser, approle where personnel.departmentId = department.id and personnel.accountId = appuser.id and appuser.appRoleId = approle.id");
        if(!StringUtils.isNullOrEmpty(departmentId)){
            sql.append(" and department.id = ? ");
            params.add(departmentId);
        }
        if(!StringUtils.isNullOrEmpty(depName)){
            sql.append(" and department.depName = ? ");
            params.add(depName);
        }
        if(!StringUtils.isNullOrEmpty(perName)){
            sql.append(" and personnel.name = ? ");
            params.add(perName);
        }

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Personnel _item = new Personnel();
            _item.setId(rs.getInt("personnel.id"));
            _item.setName(rs.getString("personnel.name"));
            _item.setSex(rs.getInt("personnel.sex"));
            _item.setWorkno(rs.getString("personnel.workno"));
            _item.setPhone(rs.getString("personnel.phone"));
            _item.setDepartmentId(rs.getInt("department.id"));
            _item.setDepartmentType(rs.getInt("department.depType"));
            _item.setDepartmentName(rs.getString("department.depName"));
            _item.setAccountId(rs.getInt("appuser.id"));
            _item.setAccountName(rs.getString("appuser.account"));
            _item.setRoleName(rs.getString("approle.roleName"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取学生列表
     * @param connection
     * @param parentDepId  二级学院id
     * @return
     * @throws Exception
     */
    @Override
    public List<Personnel> getStudentList(Connection connection, String parentDepId) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Personnel> resultList = new ArrayList<Personnel>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from personnel, department, appuser where personnel.departmentId = department.id and personnel.accountId = appuser.id and department.parentId = ?");
        params.add(parentDepId);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Personnel _item = new Personnel();
            _item.setId(rs.getInt("personnel.id"));
            _item.setName(rs.getString("personnel.name"));
            _item.setSex(rs.getInt("personnel.sex"));
            _item.setWorkno(rs.getString("personnel.workno"));
            _item.setPhone(rs.getString("personnel.phone"));
            _item.setDepartmentId(rs.getInt("department.id"));
            _item.setDepartmentType(rs.getInt("department.depType"));
            _item.setDepartmentName(rs.getString("department.depName"));
            _item.setAccountId(rs.getInt("appuser.id"));
            _item.setAccountName(rs.getString("appuser.account"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取学生列表（辅导员）
     * @param connection
     * @param colId  辅导员Id
     * @return
     * @throws Exception
     */
    @Override
    public List<Personnel> getStudentListCol(Connection connection, String colId) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Personnel> resultList = new ArrayList<Personnel>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from personnel, department, appuser where personnel.departmentId = department.id and personnel.accountId = appuser.id and department.principalId = ?");
        params.add(colId);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Personnel _item = new Personnel();
            _item.setId(rs.getInt("personnel.id"));
            _item.setName(rs.getString("personnel.name"));
            _item.setSex(rs.getInt("personnel.sex"));
            _item.setWorkno(rs.getString("personnel.workno"));
            _item.setPhone(rs.getString("personnel.phone"));
            _item.setDepartmentId(rs.getInt("department.id"));
            _item.setDepartmentType(rs.getInt("department.depType"));
            _item.setDepartmentName(rs.getString("department.depName"));
            _item.setAccountId(rs.getInt("appuser.id"));
            _item.setAccountName(rs.getString("appuser.account"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取项详情
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public Personnel getItemDetail(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        Personnel item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from personnel, department, appuser where personnel.id = ? and personnel.departmentId = department.id and personnel.accountId = appuser.id");
        params.add(id);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Personnel _item = new Personnel();
            _item.setId(rs.getInt("personnel.id"));
            _item.setName(rs.getString("personnel.name"));
            _item.setSex(rs.getInt("personnel.sex"));
            _item.setWorkno(rs.getString("personnel.workno"));
            _item.setPhone(rs.getString("personnel.phone"));
            _item.setDepartmentId(rs.getInt("department.id"));
            _item.setDepartmentType(rs.getInt("department.depType"));
            _item.setDepartmentName(rs.getString("department.depName"));
            _item.setAccountId(rs.getInt("appuser.id"));
            _item.setAccountName(rs.getString("appuser.account"));
            item = _item;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }
    /**
     * 新增
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int add(Connection connection, Personnel item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("insert into personnel (name, sex, workno, phone, departmentId, accountId) values (?, ?, ?, ?, ?, ?)");
        params.add(item.getName());
        params.add(item.getSex());
        params.add(item.getWorkno());
        params.add(item.getPhone());
        params.add(item.getDepartmentId());
        params.add(item.getAccountId());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }



    /**
     * 更新
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int update(Connection connection, Personnel item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update personnel set name = ?, sex = ?, workno = ?, phone = ?, departmentId = ? where id = ?");
        params.add(item.getName());
        params.add(item.getSex());
        params.add(item.getWorkno());
        params.add(item.getPhone());
        params.add(item.getDepartmentId());
        params.add(item.getId());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }


    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public int delete(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("delete from personnel where id = ?");
        params.add(id);


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }
}
